#!/usr/bin/python3
# -*- coding:UTF-8 -*-
"""Module DocString"""

__author__ = 'Wang Zhe'

# 导入SQLite驱动:
import sqlite3
import os
from functools import reduce


class SqlObject(object):
    """操作一个数据库，暂时只是sqlite"""

    def __init__(self, db_name):
        self._db_name = db_name
        self.db_path = str.format('%s\\%s'%(os.path.dirname(__file__),db_name))
        # print(self.db_path)

    def init_db(self):
        """初始化数据库"""

        self.delete_db()

        conn = sqlite3.connect(self.db_path)

        conn.commit()
        conn.close()

    def delete_db(self):
        if os.path.isfile(self.db_path):
            os.remove(self.db_path)

    def delete_table(self, table_name):
        """删除表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        delete_sql = str.format('DROP TABLE IF EXISTS %s' % (table_name,))
        cursor.execute(delete_sql)
        cursor.close()
        conn.close()

    def create_table(self, table_name, **table_field):
        """创建表,table_field为字段名和字段类型键值对"""
        assert len(table_field) != 0, 'table_field.length not be zero'
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        self.delete_table(table_name)

        create_sql = self.get_create_table_sql(table_name, **table_field)
        cursor.execute(create_sql)

        cursor.close()
        conn.commit()
        conn.close()

    def insert_data(self, table_name, **table_field):
        """插入数据"""
        # 连接到SQLite数据库
        # 数据库文件是test.db
        # 如果文件不存在，会自动在当前目录创建:
        conn = sqlite3.connect(self.db_path)

        # 创建一个Cursor:
        cursor = conn.cursor()

        # 继续执行一条SQL语句，插入一条记录:
        sql_str = self.get_insert_data_sql(table_name, table_field)
        cursor.execute(sql_str)

        # 通过rowcount获得插入的行数:
        print('改变了',cursor.rowcount,"行")

        # 关闭Cursor:
        cursor.close()

        # 提交事务:
        conn.commit()

        # 关闭Connection:
        conn.close()

    def select_data(self, table_name, id):
        """查询并打印数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        select_sql = str.format(
            'select * from %s where id=%d' % (table_name, id,))
        cursor.execute(select_sql)
        values = cursor.fetchall()
        print(values)
        cursor.close()
        conn.close()
    def get_create_table_sql(self, table_name, **table_field):
        """拼出创建表的sql"""
        # print(table_name,table_field)
        table_field_str = ''
        for field, field_type in table_field.items():
            if table_field_str is '':
                table_field_str = str.format(
                    '%s %s primary key not null,' % (field, field_type))
            else:
                table_field_str = str.format('%s %s %s,' % (
                    table_field_str, field, field_type))
        table_field_str = table_field_str.rstrip('\,')
        return str.format('create table %s (%s)' % (table_name, table_field_str,))

    def get_insert_data_sql(self, table_name, test_dict):
        def to_string(num):
            """把变量转为str"""
            return str(num)
        field_str = ','.join(list(map(to_string, test_dict.keys())))
        value_str = tuple(test_dict.values())
        return str.format('insert into %s (%s) values %s' % (table_name, field_str, value_str))

    def get_score_in(self,table_name, low,high):
        """查询并打印数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        select_sql = str.format(
            'select * from %s where score between %d and %d order by score asc' % (table_name, low,high))
        cursor.execute(select_sql)
        values = cursor.fetchall()
        print(values)
        cursor.close()
        conn.close()

def main():
    """Func DocString"""
    db_name = 'test.db'
    table_name = 'user'
    obj = SqlObject(db_name)
    obj.init_db()
    obj.create_table(table_name,id = 'int',name = 'varchar(20)',score = 'int')
    obj.insert_data(table_name,id=1, name='wangzhe',score = 100)
    obj.insert_data(table_name,id=2, name='chenqianqian',score = 99)
    obj.insert_data(table_name,id=3, name='wangyingying',score = 98)
    obj.get_score_in(table_name, 99,100)
    obj.get_score_in(table_name, 98,99)
    obj.get_score_in(table_name, 98,100)
    # obj.delete_db()


if __name__ == '__main__':
    main()
